package com.sinosoft.emplmis.jobless.web;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.sinosoft.emplmis.application.web.utils.ExcelUtils;
import com.sinosoft.emplmis.jobless.entity.JoblessInfoEntity;
import com.sinosoft.emplmis.jobless.service.JoblessInfoService;
import com.sinosoft.emplmis.jobless.web.dto.JoblessSearchData;
import com.sinosoft.framework.dict.domain.DictCommonEntity;
import com.sinosoft.framework.dict.service.DictService;

/**
 * 失业人员管理信息excel导出
 * @author liux
 *
 */
@Controller
@RequestMapping("jobless/excel")
public class JoblessExcelController {

	/**
	 * 失业人员信息服务类
	 */
	@Autowired
	private JoblessInfoService joblessInfoService;
	
	/**
	 * 字典服务
	 */
	@Autowired
	private DictService dictService;
	
	/**
	 * 导出失业人员基本信息
	 * @return
	 * @throws UnsupportedEncodingException 
	 */
	@RequestMapping("/exportJoblessInfoList")
	@ResponseBody
	public String exportJoblessInfoList(HttpServletRequest request,HttpServletResponse response, JoblessSearchData searchData) throws UnsupportedEncodingException{

		request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="登记 (转移登记)失业人员动态管理情况汇总表.xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
        String street = request.getParameter("street");
        String community = request.getParameter("community");
        searchData.setStreet(street);
        searchData.setCommunity(community);
        List<JoblessInfoEntity> joblessInfoEntityList = joblessInfoService.joblessInfoList(searchData);
        // 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(fileName);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);  
      
        cell = row.createCell(1);  
        cell.setCellValue("性别");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(3);  
        cell.setCellValue("联系方式");  
        cell.setCellStyle(style);
        
        cell = row.createCell(4);  
        cell.setCellValue("学历");  
        cell.setCellStyle(style);
        
        cell = row.createCell(5);  
        cell.setCellValue("失业时间");  
        cell.setCellStyle(style);

        cell = row.createCell(6);  
        cell.setCellValue("困难人员");  
        cell.setCellStyle(style);
        
        cell = row.createCell(7);  
        cell.setCellValue("减少原因");  
        cell.setCellStyle(style);
        
        cell = row.createCell(8);  
        cell.setCellValue("放弃求职审核");  
        cell.setCellStyle(style);
        
        cell = row.createCell(9);  
        cell.setCellValue("减少时间");  
        cell.setCellStyle(style);
        
        cell = row.createCell(10);  
        cell.setCellValue("街道");  
        cell.setCellStyle(style);
        
        cell = row.createCell(11);  
        cell.setCellValue("社区");  
        cell.setCellStyle(style);
        
        cell = row.createCell(12);  
        cell.setCellValue("备注");  
        cell.setCellStyle(style);
        
        if(joblessInfoEntityList.size() > 0){
			//性别字典
			List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");
			//学历字典表
			List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
			//街道字典表
			List<DictCommonEntity> streetDataList = dictService.findSingleDictDetail("dict_street");
			//社区字典表
			List<DictCommonEntity> communityDataList = dictService.findSingleDictDetail("dict_community");
			//困难人员字典
			List<DictCommonEntity> knryDataList = dictService.findSingleDictDetail("dict_knry");
			//减少原因字典
			List<DictCommonEntity> removereasonDataList = dictService.findSingleDictDetail("dict_removereason");
			//放弃求职审核字典
			List<DictCommonEntity> approvestateDataList = dictService.findSingleDictDetail("dict_approvestate");
			for(int i = 0; i < joblessInfoEntityList.size(); i++){
				row = sheet.createRow((int) i + 1); 
				JoblessInfoEntity joblessInfo = joblessInfoEntityList.get(i);
				row.createCell(0).setCellValue(joblessInfo.getName());
				//获取性别名称
				ExcelUtils.getDictNameByCode(row, genderDataList, joblessInfo.getSex(),1);
				row.createCell(2).setCellValue(joblessInfo.getIdcard());
				row.createCell(3).setCellValue(joblessInfo.getPhone());
				//获取学历名称
				ExcelUtils.getDictNameByCode(row, educationDataList, joblessInfo.getEducation(),4);
				row.createCell(5).setCellValue(joblessInfo.getUnemploymentdate());
				//获取困难人员名称
				ExcelUtils.getDictNameByCode(row, knryDataList, joblessInfo.getKnry(), 6);
				//获取减少原因名称
				ExcelUtils.getDictNameByCode(row, removereasonDataList, joblessInfo.getRemovereason(), 7);
				//获取放弃求职审核名称
				ExcelUtils.getDictNameByCode(row, approvestateDataList, joblessInfo.getApprove(), 8);
				row.createCell(9).setCellValue(joblessInfo.getRemovedate());
				//获取街道名称
				ExcelUtils.getDictNameByCode(row, streetDataList, joblessInfo.getStreet(),10);
				//获取街道名称
				ExcelUtils.getDictNameByCode(row, communityDataList, joblessInfo.getCommunity(),11);
				row.createCell(12).setCellValue(joblessInfo.getRemark());
			}
				
			}
			 try  
		        {  
		        	response.setContentType(contentType);
		        	response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
		        	OutputStream outputStream = response.getOutputStream();
		            wb.write(outputStream);  
		            outputStream.close();  
		        }  
		        catch (Exception e)  
		        {  
		            e.printStackTrace();  
		        }
		return null;
	}
	
	/**
	 * 导出城乡就业困难人员信息
	 * @param request
	 * @param response
	 * @param searchData
	 * @return
	 * @throws UnsupportedEncodingException
	 */
	@RequestMapping("/exportDifficultyInfoList")
	@ResponseBody
	public String exportDifficultyInfoList(HttpServletRequest request,HttpServletResponse response,JoblessSearchData searchData) throws UnsupportedEncodingException{
		
		request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="城乡就业困难人员信息.xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
        String street = request.getParameter("street");
        String community = request.getParameter("community");
        searchData.setStreet(street);
        searchData.setCommunity(community);
        List<JoblessInfoEntity> joblessInfoEntityList = joblessInfoService.difficultyInfoList(searchData);
     // 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(fileName);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);  
      
        cell = row.createCell(1);  
        cell.setCellValue("性别");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(3);  
        cell.setCellValue("联系方式");  
        cell.setCellStyle(style);
        
        cell = row.createCell(4);  
        cell.setCellValue("学历");  
        cell.setCellStyle(style);
        
        cell = row.createCell(5);  
        cell.setCellValue("失业时间");  
        cell.setCellStyle(style);

        cell = row.createCell(6);  
        cell.setCellValue("转困难人员时间");  
        cell.setCellStyle(style);
        
        cell = row.createCell(7);  
        cell.setCellValue("困难类型");  
        cell.setCellStyle(style);
        
        cell = row.createCell(8);  
        cell.setCellValue("较少原因");  
        cell.setCellStyle(style);
        
        cell = row.createCell(9);  
        cell.setCellValue("减少时间");  
        cell.setCellStyle(style);
        
        cell = row.createCell(10);  
        cell.setCellValue("街道");  
        cell.setCellStyle(style);
        
        cell = row.createCell(11);  
        cell.setCellValue("社区");  
        cell.setCellStyle(style);
        
        cell = row.createCell(12);  
        cell.setCellValue("备注");  
        cell.setCellStyle(style);
        
        if(joblessInfoEntityList.size() > 0){
			//性别字典
			List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");
			//学历字典表
			List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
			//街道字典表
			List<DictCommonEntity> streetDataList = dictService.findSingleDictDetail("dict_street");
			//社区字典表
			List<DictCommonEntity> communityDataList = dictService.findSingleDictDetail("dict_community");
			//困难类型字典
			List<DictCommonEntity> difficultytypeDataList = dictService.findSingleDictDetail("dict_difficultytype");
			//减少原因字典
			List<DictCommonEntity> removereasonDataList = dictService.findSingleDictDetail("dict_removereason");
			for(int i = 0; i < joblessInfoEntityList.size(); i++){
				row = sheet.createRow((int) i + 1); 
				JoblessInfoEntity joblessInfo = joblessInfoEntityList.get(i);
				row.createCell(0).setCellValue(joblessInfo.getName());
				//获取性别名称
				ExcelUtils.getDictNameByCode(row, genderDataList, joblessInfo.getSex(),1);
				row.createCell(2).setCellValue(joblessInfo.getIdcard());
				row.createCell(3).setCellValue(joblessInfo.getPhone());
				//获取学历名称
				ExcelUtils.getDictNameByCode(row, educationDataList, joblessInfo.getEducation(),4);
				row.createCell(5).setCellValue(joblessInfo.getUnemploymentdate());
				row.createCell(6).setCellValue(joblessInfo.getKnrydate());
				//获取困难类型名称
				ExcelUtils.getDictNameByCode(row, difficultytypeDataList, joblessInfo.getDifficultytype(), 7);
				//获取减少原因名称
				ExcelUtils.getDictNameByCode(row, removereasonDataList, joblessInfo.getRemovereason(), 8);
				row.createCell(9).setCellValue(joblessInfo.getRemovedate());
				//获取街道名称
				ExcelUtils.getDictNameByCode(row, streetDataList, joblessInfo.getStreet(),10);
				//获取街道名称
				ExcelUtils.getDictNameByCode(row, communityDataList, joblessInfo.getCommunity(),11);
				row.createCell(12).setCellValue(joblessInfo.getRemark());
			}
				
			}
			 try  
		        {  
		        	response.setContentType(contentType);
		        	response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
		        	OutputStream outputStream = response.getOutputStream();
		            wb.write(outputStream);  
		            outputStream.close();  
		        }  
		        catch (Exception e)  
		        {  
		            e.printStackTrace();  
		        }
		return null;
	}
	
	/*
	 * 导出放弃求职人员信息
	 */
	@RequestMapping(value="/exportJoblessGiveup")
	@ResponseBody
	public ModelAndView exportJoblessGiveup(HttpServletRequest request,HttpServletResponse response,JoblessSearchData searchData) throws UnsupportedEncodingException{
		
		request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="放弃求职人员情况表.xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
        String street = request.getParameter("street");
        String community = request.getParameter("community");
        searchData.setStreet(street);
        searchData.setCommunity(community);
		List<JoblessInfoEntity> joblessGiveupEntityList = joblessInfoService.joblessGiveupList(searchData);
		// 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(fileName);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        
        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("序号");  
        cell.setCellStyle(style);  

        cell = row.createCell(1);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);  
      
        cell = row.createCell(2);  
        cell.setCellValue("性别");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(3);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(4);  
        cell.setCellValue("文化程度");  
        cell.setCellStyle(style);
        
        cell = row.createCell(5);  
        cell.setCellValue("登记时间");  
        cell.setCellStyle(style);       
        
        cell = row.createCell(6);  
        cell.setCellValue("联系电话");  
        cell.setCellStyle(style);

        cell = row.createCell(7);  
        cell.setCellValue("邮政编码");  
        cell.setCellStyle(style);
        
        cell = row.createCell(8);  
        cell.setCellValue("户籍地址");  
        cell.setCellStyle(style);
        
        cell = row.createCell(9);  
        cell.setCellValue("减少原因");  
        cell.setCellStyle(style);
        
        cell = row.createCell(10);  
        cell.setCellValue("审核状态");  
        cell.setCellStyle(style);
        
        cell = row.createCell(11);  
        cell.setCellValue("街道");  
        cell.setCellStyle(style);
        
        cell = row.createCell(12);  
        cell.setCellValue("社区");  
        cell.setCellStyle(style);
        
		if(joblessGiveupEntityList.size() > 0){
			//性别字典
			List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");
			//学历字典表
			List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
			//困难人员字典表
			List<DictCommonEntity> knryDataList = dictService.findSingleDictDetail("dict_knry");
			//减少原因字典表
			List<DictCommonEntity> removereasonDataList = dictService.findSingleDictDetail("dict_removereason");
			//放弃求职审核字典表
			List<DictCommonEntity> approvestateDataList = dictService.findSingleDictDetail("dict_approvestate");
			//街道字典表
			List<DictCommonEntity> streetDataList = dictService.findSingleDictDetail("dict_street");
			//社区字典表
			List<DictCommonEntity> communityDataList = dictService.findSingleDictDetail("dict_community");
			
			for(int i = 0; i < joblessGiveupEntityList.size(); i++){
				row = sheet.createRow((int) i + 1); 
				JoblessInfoEntity joblessInfo = joblessGiveupEntityList.get(i);
				//序号
//				row.createCell(0).setCellValue(i+1);
				cell = row.createCell(0);
				cell.setCellValue(i+1);
				cell.setCellStyle(style);
				//姓名
				row.createCell(1).setCellValue(joblessInfo.getName());
				//获取性别名称
				ExcelUtils.getDictNameByCode(row, genderDataList, joblessInfo.getSex(),2);
				//身份证号
				row.createCell(3).setCellValue(joblessInfo.getIdcard());
				//获取学历名称
				ExcelUtils.getDictNameByCode(row, educationDataList, joblessInfo.getEducation(),4);
				//登记时间
				row.createCell(5).setCellValue(joblessInfo.getRegisterdate());
				//联系电话
				row.createCell(6).setCellValue(joblessInfo.getPhone());
				//邮政编码
				row.createCell(7).setCellValue(joblessInfo.getZipcode());
				//户籍地址
				row.createCell(8).setCellValue(joblessInfo.getResidenceaddress());
				//获取减少原因名称
				ExcelUtils.getDictNameByCode(row, removereasonDataList, joblessInfo.getRemovereason(),9);
				//获取放弃求职审核名称
				ExcelUtils.getDictNameByCode(row, approvestateDataList, joblessInfo.getApprove(),10);
				//获取街道名称
				ExcelUtils.getDictNameByCode(row, streetDataList, joblessInfo.getStreet(),11);
				//获取社区名称
				ExcelUtils.getDictNameByCode(row, communityDataList, joblessInfo.getCommunity(),12);
				
			}
			
		}
		 try  
	        {  
	        	response.setContentType(contentType);
	        	response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
	        	OutputStream outputStream = response.getOutputStream();
	            wb.write(outputStream);  
	            outputStream.close();  
	        }  
	        catch (Exception e)  
	        {  
	            e.printStackTrace();  
	        }
		return null;  
	}
	
	/*
	 * 打印（导出）放弃求职人员登记表：用改写模板的方式
	 */
	@RequestMapping(value="/exportRegistrySheet")
	@ResponseBody
	public ModelAndView exportRegistrySheet(HttpServletRequest request,HttpServletResponse response,JoblessSearchData searchData) throws UnsupportedEncodingException{
		
        String unemployedId = request.getParameter("unemployedId");       
		JoblessInfoEntity joblessInfo = joblessInfoService.selectByPrimaryKey(unemployedId);
		
		// 创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
		
		FileInputStream fis = null;
		try {
//			fis = new FileInputStream("D:/registrySheet_Template.xls");
			String filePath = request.getSession().getServletContext().getRealPath("sampleExcel/registrySheet.xls");
			fis = new FileInputStream(filePath);			
			POIFSFileSystem poi_fs = new POIFSFileSystem(fis);          
	        wb = new HSSFWorkbook(poi_fs);  
	        
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}            
        
		// 设置正文样式
        HSSFCellStyle style = wb.createCellStyle(); 
        // 设置边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
        // 创建水平和垂直居中格式  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 生成一个字体  
        HSSFFont font = wb.createFont();  
        font.setFontHeightInPoints((short) 14);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 
        font.setFontName("仿宋");
        // 把字体应用到当前的样式  
        style.setFont(font);  
        
        //设置正文样式2
        HSSFCellStyle style2 = wb.createCellStyle(); 
        // 创建水平和垂直居中格式  
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 把字体应用到当前的样式  
        style2.setFont(font); 
        
        // 设置正文样式3
        HSSFCellStyle style3 = wb.createCellStyle(); 
        // 设置边框
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        // 创建水平和垂直居中格式  
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 把字体应用到当前的样式  
        style3.setFont(font);  
        
        HSSFSheet sheet = wb.createSheet("放弃求职登记表");  
        sheet =  wb.getSheetAt(0);  

        HSSFRow row = sheet.getRow(1); 
        // 姓名
        HSSFCell cell = row.getCell(1);        
        cell.setCellValue(joblessInfo.getName()); 
        cell.setCellStyle(style);  
        // 身份证号
        cell = row.getCell(5); 
        cell.setCellValue(joblessInfo.getIdcard());
        cell.setCellStyle(style);  
        
        //性别字典
        List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");	
        // 获取“性别”名称
		row = sheet.getRow(2); 
		ExcelUtils.getDictNameByCode(row, genderDataList, joblessInfo.getSex(),1);
		row.getCell(1).setCellStyle(style);  
        // 出生年月
		cell = row.getCell(3); 
        cell.setCellValue(joblessInfo.getBirth()); 
        cell.setCellStyle(style);  
        
        //学历字典表
        List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
        ExcelUtils.getDictNameByCode(row, educationDataList, joblessInfo.getEducation(),5);
        row.getCell(5).setCellStyle(style);  
        
        // 所学专业
        cell = row.getCell(7); 
        cell.setCellValue(joblessInfo.getMajor()); 
        cell.setCellStyle(style);  
        
        // 登记时间
        row = sheet.getRow(3);
        cell = row.getCell(1); 
        cell.setCellValue(joblessInfo.getRegisterdate()); 
        cell.setCellStyle(style);  
        
        // 联系电话
        cell = row.getCell(4); 
        cell.setCellValue(joblessInfo.getPhone()); 
        cell.setCellStyle(style);  
        
        // 邮政编码
        cell = row.getCell(7); 
        cell.setCellValue(joblessInfo.getZipcode());
        cell.setCellStyle(style);  
        
        // 户籍地址
        row = sheet.getRow(4);
        cell = row.getCell(1); 
        cell.setCellValue(joblessInfo.getResidenceaddress()); 
        cell.setCellStyle(style);  
        
        // 居住地址
        cell = row.getCell(5); 
        cell.setCellValue(joblessInfo.getAddress()); 
        cell.setCellStyle(style);  
        
        // 放弃求职原因
        row = sheet.getRow(5);
        cell = row.getCell(1); 
        cell.setCellValue(joblessInfo.getQuitcause()); 
        cell.setCellStyle(style);  
        
        // 社区意见
        row = sheet.getRow(7);
        cell = row.getCell(0); 
        cell.setCellValue(joblessInfo.getCommunityopinion()); 
        cell.setCellStyle(style3);  
        
        // 社保所意见
        cell = row.getCell(4); 
        cell.setCellValue(joblessInfo.getBureauopinion()); 
        cell.setCellStyle(style3);  
        
        // 就业助理员（登记人）
        row = sheet.getRow(9);
        cell = row.getCell(6); 
        cell.setCellValue(joblessInfo.getRegistrant()); 
        cell.setCellStyle(style2);  
        
        request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="放弃求职登记表_"+joblessInfo.getName()+".xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
              
        try {
			response.setContentType(contentType);
	        response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
	        OutputStream outputStream = response.getOutputStream();
	        wb.write(outputStream);  
	        outputStream.close();  
	    } catch (Exception e) {  
	    	e.printStackTrace();  
	    }
		
		return null;  
	}
	
	/*
	 * 打印（导出）放弃求职人员登记表：纯写excel
	 */
	@RequestMapping(value="/exportRegistryForm")
	@ResponseBody
	public ModelAndView exportRegistryForm(HttpServletRequest request,HttpServletResponse response,JoblessSearchData searchData) throws UnsupportedEncodingException{
		
		request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="放弃求职人员登记表.xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
//        String street = request.getParameter("street");
//        String community = request.getParameter("community");
//        searchData.setStreet(street);
//        searchData.setCommunity(community);
        
        String unemployedId = request.getParameter("unemployedId");
        
		JoblessInfoEntity joblessInfo = joblessInfoService.selectByPrimaryKey(unemployedId);
		// 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(fileName);  
        // 设置表格默认列宽度  
//        sheet.setDefaultColumnWidth((short) 10); 
        
        // 第三步，创建单元格，并设置标题样式
        HSSFCellStyle header_style = wb.createCellStyle(); 
//        header_style.setFillForegroundColor(IndexedColors.BLUE.getIndex());  
//        header_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        header_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        header_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中       
        // 生成一个字体  
        HSSFFont header_font = wb.createFont();  
        header_font.setFontHeightInPoints((short) 16);  
        header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
        header_font.setFontName("宋体");
        // 把字体应用到当前的样式  
        header_style.setFont(header_font);  
        
        // 设置正文样式
        HSSFCellStyle style = wb.createCellStyle(); 
        // 设置边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        // 创建水平和垂直居中格式  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 生成一个字体  
        HSSFFont font = wb.createFont();  
        font.setFontHeightInPoints((short) 14);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 
        font.setFontName("宋体");
        // 把字体应用到当前的样式  
        style.setFont(font);  
        
        // 设置正文样式2
        HSSFCellStyle style2 = wb.createCellStyle(); 
        // 设置边框
        style2.setBorderBottom(HSSFCellStyle.BORDER_NONE);  
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        // 创建水平和垂直居中格式  
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); 
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 把字体应用到当前的样式  
        style2.setFont(font);  
        
        // 设置正文样式3
        HSSFCellStyle style3 = wb.createCellStyle(); 
        // 设置边框
        style3.setBorderBottom(HSSFCellStyle.BORDER_NONE);  
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style3.setBorderTop(HSSFCellStyle.BORDER_NONE);  
        // 创建水平和垂直居中格式  
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 把字体应用到当前的样式  
        style3.setFont(font); 
        
        // 设置正文样式4
        HSSFCellStyle style4 = wb.createCellStyle(); 
        // 设置边框
        style4.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style4.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style4.setBorderTop(HSSFCellStyle.BORDER_NONE);  
        // 创建水平和垂直居中格式  
        style4.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 把字体应用到当前的样式  
        style4.setFont(font); 
        
        // 设置正文样式5
        HSSFCellStyle style5 = wb.createCellStyle(); 
        // 设置边框
        style5.setBorderBottom(HSSFCellStyle.BORDER_NONE);  
        style5.setBorderLeft(HSSFCellStyle.BORDER_NONE);  
        style5.setBorderRight(HSSFCellStyle.BORDER_NONE);  
        style5.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        // 创建水平和垂直居中格式  
        style5.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 把字体应用到当前的样式  
        style5.setFont(font); 
        
        // 第四步，在sheet中添加表头第1行，生成标题行
        HSSFRow row = sheet.createRow((int) 0);  
        //设置行高为52px
        row.setHeightInPoints(52); 
        HSSFCell cell = null;
        for(int i=0;i<8;i++){
        	cell = row.createCell(i); 
        }
        //合并单元格  
        //新版用法 3.8版  
//      sheet.addMergedRegion(new CellRangeAddress(     
//              1, //first row (0-based)  from 行     
//              2, //last row  (0-based)  to 行     
//              1, //first column (0-based) from 列     
//              1  //last column  (0-based)  to 列     
//      ));         
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));  
        sheet.getNumMergedRegions();  
        sheet.getRow(0).getCell(0).setCellStyle(header_style);  
        sheet.getRow(0).getCell(0).setCellValue("失业人员放弃求职情况登记表"); 
        sheet.autoSizeColumn(0,true);//第一列是合并列 
        
        
        // 生成正文行（第2行）
        HSSFRow row2 = sheet.createRow((int) 1); 
        row2.setHeightInPoints(28); //设置行高为28px
        
        for(int i=0;i<8;i++){
        	cell = row2.createCell(i); 
        }
        
        sheet.setColumnWidth((short) 0, (short) 10 * 256);
//        cell = row2.createCell(0);  
//        cell.setCellValue("姓名");  
//        cell.setCellStyle(style);        
        sheet.getRow(1).getCell(0).setCellValue("姓名");
        sheet.getRow(1).getCell(0).setCellStyle(style); 
        
        sheet.setColumnWidth((short) 1, (short) 10 * 256);
        sheet.setColumnWidth((short) 2, (short) 10 * 256);
        sheet.addMergedRegion(new CellRangeAddress(1,1,1,2));  
        sheet.getNumMergedRegions();  
        // 姓名
        sheet.getRow(1).getCell(1).setCellValue(joblessInfo.getName()); 
        sheet.getRow(1).getCell(1).setCellStyle(style);  
        sheet.getRow(1).getCell(2).setCellStyle(style);   
        
        sheet.setColumnWidth((short) 3, (short) 12 * 256);
        sheet.setColumnWidth((short) 4, (short) 10 * 256);
        sheet.addMergedRegion(new CellRangeAddress(1,1,3,4)); 
        sheet.getNumMergedRegions();
        sheet.getRow(1).getCell(3).setCellValue("身份证号码"); 
        sheet.getRow(1).getCell(3).setCellStyle(style);  
        sheet.getRow(1).getCell(4).setCellStyle(style); 
        
        sheet.setColumnWidth((short) 5, (short) 10 * 256);
        sheet.setColumnWidth((short) 6, (short) 10 * 256);
        sheet.setColumnWidth((short) 7, (short) 12 * 256);
        sheet.addMergedRegion(new CellRangeAddress(1,1,5,7)); 
        sheet.getNumMergedRegions();
        // 身份证号
        sheet.getRow(1).getCell(5).setCellValue(joblessInfo.getIdcard()); 
        sheet.getRow(1).getCell(5).setCellStyle(style);  
        sheet.getRow(1).getCell(6).setCellStyle(style); 
        sheet.getRow(1).getCell(7).setCellStyle(style); 
        
        // 生成正文行（第3行）
        HSSFRow row3 = sheet.createRow((int) 2); 
        row3.setHeightInPoints(44); //设置行高为44px        
        for(int i=0;i<8;i++){
        	cell = row3.createCell(i); 
        }
      
        //性别字典
        List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");
		//学历字典表
		List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
        
        
        sheet.getRow(2).getCell(0).setCellValue("性别");
        sheet.getRow(2).getCell(0).setCellStyle(style);
        // 获取“性别”名称
//        sheet.getRow(2).getCell(1).setCellValue(joblessInfo.getSex());
        ExcelUtils.getDictNameByCode(row3, genderDataList, joblessInfo.getSex(),1);
        sheet.getRow(2).getCell(1).setCellStyle(style);
        
        sheet.getRow(2).getCell(2).setCellValue("出生\n年月");
        style.setWrapText(true); 
        sheet.getRow(2).getCell(2).setCellStyle(style);
        // 出生年月
        sheet.getRow(2).getCell(3).setCellValue(joblessInfo.getBirth());
        sheet.getRow(2).getCell(3).setCellStyle(style);
        
        sheet.getRow(2).getCell(4).setCellValue("文化\n程度");
        style.setWrapText(true); 
        sheet.getRow(2).getCell(4).setCellStyle(style);
        // 获取“学历”名称
        ExcelUtils.getDictNameByCode(row3, educationDataList, joblessInfo.getEducation(),5);
        sheet.getRow(2).getCell(5).setCellStyle(style);
        
        sheet.getRow(2).getCell(6).setCellValue("所学\n专业");
        style.setWrapText(true); 
        sheet.getRow(2).getCell(6).setCellStyle(style);
        // 所学专业
        sheet.getRow(2).getCell(7).setCellValue(joblessInfo.getMajor());
        sheet.getRow(2).getCell(7).setCellStyle(style);


        // 生成正文行（第4行）
        HSSFRow row4 = sheet.createRow((int) 3); 
        row4.setHeightInPoints(44); //设置行高为44px        
        for(int i=0;i<8;i++){
        	cell = row4.createCell(i); 
        }
        sheet.getRow(3).getCell(0).setCellValue("登记\n时间");
        style.setWrapText(true); 
        sheet.getRow(3).getCell(0).setCellStyle(style);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(3,3,1,2));  
        sheet.getNumMergedRegions();  
        // 登记时间
        sheet.getRow(3).getCell(1).setCellValue(joblessInfo.getRegisterdate()); 
        sheet.getRow(3).getCell(1).setCellStyle(style);  
        sheet.getRow(3).getCell(2).setCellStyle(style);
        
        sheet.getRow(3).getCell(3).setCellValue("联系\n电话");
        style.setWrapText(true); 
        sheet.getRow(3).getCell(3).setCellStyle(style);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(3,3,4,5));  
        sheet.getNumMergedRegions();  
        // 联系电话
        sheet.getRow(3).getCell(4).setCellValue(joblessInfo.getPhone()); 
        sheet.getRow(3).getCell(4).setCellStyle(style);  
        sheet.getRow(3).getCell(5).setCellStyle(style);
     
        sheet.getRow(3).getCell(6).setCellValue("邮政\n编码");
        style.setWrapText(true); 
        sheet.getRow(3).getCell(6).setCellStyle(style);
        // 邮政编码
        sheet.getRow(3).getCell(7).setCellValue(joblessInfo.getZipcode()); 
        sheet.getRow(3).getCell(7).setCellStyle(style);  

        
        // 生成正文行（第5行）
        HSSFRow row5 = sheet.createRow((int) 4); 
        row5.setHeightInPoints(44); //设置行高为44px        
        for(int i=0;i<8;i++){
        	cell = row5.createCell(i); 
        }
        row5.getCell(0).setCellValue("户籍\n地址");
        style.setWrapText(true); 
        row5.getCell(0).setCellStyle(style);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(4,4,1,3));  
        sheet.getNumMergedRegions();  
        // 户籍地址
        row5.getCell(1).setCellValue(joblessInfo.getResidenceaddress()); 
        row5.getCell(1).setCellStyle(style);  
        row5.getCell(2).setCellStyle(style);
        row5.getCell(3).setCellStyle(style);
        
        sheet.getRow(4).getCell(4).setCellValue("居住\n地址");
        style.setWrapText(true); 
        row5.getCell(4).setCellStyle(style);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(4,4,5,7));  
        sheet.getNumMergedRegions();  
        // 户籍地址
        row5.getCell(5).setCellValue(joblessInfo.getAddress()); 
        row5.getCell(5).setCellStyle(style);  
        row5.getCell(6).setCellStyle(style);
        row5.getCell(7).setCellStyle(style);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(4,4,1,3));  
        sheet.getNumMergedRegions();  
        // 户籍地址
        row5.getCell(1).setCellValue(joblessInfo.getResidenceaddress()); 
        row5.getCell(1).setCellStyle(style);  
        row5.getCell(2).setCellStyle(style);
        row5.getCell(3).setCellStyle(style);
        

        // 生成正文行（第6行）
        HSSFRow row6 = sheet.createRow((int) 5); 
        row6.setHeightInPoints(180); //设置行高为180px        
        for(int i=0;i<8;i++){
        	cell = row6.createCell(i); 
        }
        row6.getCell(0).setCellValue("放\n弃\n求\n职\n原\n因");
        style.setWrapText(true); 
        row6.getCell(0).setCellStyle(style); 
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(5,5,1,7));  
        sheet.getNumMergedRegions();  
        // 放弃求职原因
        row6.getCell(1).setCellValue(joblessInfo.getQuitcause()); 
        row6.getCell(1).setCellStyle(style);  
        row6.getCell(2).setCellStyle(style);
        row6.getCell(3).setCellStyle(style);
        row6.getCell(4).setCellStyle(style);  
        row6.getCell(5).setCellStyle(style);
        row6.getCell(6).setCellStyle(style);
        row6.getCell(7).setCellStyle(style);
        
        // 生成正文行（第7行）
        HSSFRow row7 = sheet.createRow((int) 6); 
        row7.setHeightInPoints(28); //设置行高为28px        
        for(int i=0;i<8;i++){
        	cell = row7.createCell(i); 
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(6,6,0,3));  
        sheet.getNumMergedRegions();  
        
        row7.getCell(0).setCellValue("社区意见：");
        row7.getCell(0).setCellStyle(style2);
        row7.getCell(1).setCellStyle(style2);
        row7.getCell(2).setCellStyle(style2);
        row7.getCell(3).setCellStyle(style2);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(6,6,4,7));  
        sheet.getNumMergedRegions();  
        
        row7.getCell(4).setCellValue("社保所意见：");
        row7.getCell(4).setCellStyle(style2);
        row7.getCell(5).setCellStyle(style2);
        row7.getCell(6).setCellStyle(style2);
        row7.getCell(7).setCellStyle(style2);
        
        // 生成正文行（第8行）
        HSSFRow row8 = sheet.createRow((int) 7); 
        row8.setHeightInPoints(160); //设置行高为160px        
        for(int i=0;i<8;i++){
        	cell = row8.createCell(i); 
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(7,7,0,3));  
        sheet.getNumMergedRegions();  

        //社区意见
        row8.getCell(0).setCellValue(joblessInfo.getCommunityopinion()); 
        row8.getCell(0).setCellStyle(style3);
        row8.getCell(1).setCellStyle(style3);
        row8.getCell(2).setCellStyle(style3);
        row8.getCell(3).setCellStyle(style3);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(7,7,4,7));  
        sheet.getNumMergedRegions();  

        //社保所意见
        row8.getCell(4).setCellValue(joblessInfo.getBureauopinion()); 
        row8.getCell(4).setCellStyle(style3);
        row8.getCell(5).setCellStyle(style3);
        row8.getCell(6).setCellStyle(style3);
        row8.getCell(7).setCellStyle(style3);
        
        // 生成正文行（第9行）
        HSSFRow row9 = sheet.createRow((int) 8); 
        row9.setHeightInPoints(28); //设置行高为28px        
        for(int i=0;i<8;i++){
        	cell = row9.createCell(i); 
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(8,8,0,3));  
        sheet.getNumMergedRegions();  
        row9.getCell(0).setCellValue("年   月   日 （公章）"); 
        row9.getCell(0).setCellStyle(style4);  
        row9.getCell(1).setCellStyle(style4);       
        row9.getCell(2).setCellStyle(style4);  
        row9.getCell(3).setCellStyle(style4); 
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(8,8,4,7));  
        sheet.getNumMergedRegions();  
        row9.getCell(4).setCellValue("年   月   日 （公章）"); 
        row9.getCell(4).setCellStyle(style4);  
        row9.getCell(5).setCellStyle(style4);       
        row9.getCell(6).setCellStyle(style4);  
        row9.getCell(7).setCellStyle(style4); 
        
        // 生成正文行（第10行）
        HSSFRow row10 = sheet.createRow((int) 9); 
        row10.setHeightInPoints(23); //设置行高为23px        
        for(int i=0;i<8;i++){
        	cell = row10.createCell(i); 
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(9,9,4,5));  
        sheet.getNumMergedRegions();  
        row10.getCell(4).setCellValue("就业助理员签字："); 
        row10.getCell(4).setCellStyle(style5);  
        row10.getCell(5).setCellStyle(style5);        
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(9,9,6,7));  
        sheet.getNumMergedRegions();  
        //就业助理员（登记人）
        row10.getCell(6).setCellValue(joblessInfo.getRegistrant()); 
        row10.getCell(6).setCellStyle(style5); 
        row10.getCell(7).setCellStyle(style5); 
        
		
		try {
			response.setContentType(contentType);
	        response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
	        OutputStream outputStream = response.getOutputStream();
	        wb.write(outputStream);  
	        outputStream.close();  
	    } catch (Exception e) {  
	    	e.printStackTrace();  
	    }
		
		return null;  
	}
	
	/*
	 * 导出客观原因减少信息
	 */
	@RequestMapping(value="/exportJoblessReduce")
	@ResponseBody
	public ModelAndView exportJoblessReduce(HttpServletRequest request,HttpServletResponse response,JoblessSearchData searchData) throws UnsupportedEncodingException{
		
		request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="客观原因减少人员情况表.xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
        String street = request.getParameter("street");
        String community = request.getParameter("community");
        searchData.setStreet(street);
        searchData.setCommunity(community);
		List<JoblessInfoEntity> joblessGiveupEntityList = joblessInfoService.joblessReduceList(searchData);
		// 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(fileName);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  

        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);  
      
        cell = row.createCell(1);  
        cell.setCellValue("性别");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(3);  
        cell.setCellValue("联系方式");  
        cell.setCellStyle(style);
        
        cell = row.createCell(4);  
        cell.setCellValue("学历");  
        cell.setCellStyle(style);       
        
        cell = row.createCell(5);  
        cell.setCellValue("失业时间");  
        cell.setCellStyle(style);

        cell = row.createCell(6);  
        cell.setCellValue("困难人员");  
        cell.setCellStyle(style);
        
        cell = row.createCell(7);  
        cell.setCellValue("择业意向");  
        cell.setCellStyle(style);
        
        cell = row.createCell(8);  
        cell.setCellValue("减少原因");  
        cell.setCellStyle(style);
        
        cell = row.createCell(9);  
        cell.setCellValue("减少时间");  
        cell.setCellStyle(style);
        
        cell = row.createCell(10);  
        cell.setCellValue("街道");  
        cell.setCellStyle(style);
        
        cell = row.createCell(11);  
        cell.setCellValue("社区");  
        cell.setCellStyle(style);
        
        cell = row.createCell(12);  
        cell.setCellValue("备注");  
        cell.setCellStyle(style);
        
		if(joblessGiveupEntityList.size() > 0){
			//性别字典
			List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");
			//学历字典表
			List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
			//困难人员字典表
			List<DictCommonEntity> knryDataList = dictService.findSingleDictDetail("dict_knry");
			//减少原因字典表
			List<DictCommonEntity> removereasonDataList = dictService.findSingleDictDetail("dict_removereason");
			//街道字典表
			List<DictCommonEntity> streetDataList = dictService.findSingleDictDetail("dict_street");
			//社区字典表
			List<DictCommonEntity> communityDataList = dictService.findSingleDictDetail("dict_community");
			for(int i = 0; i < joblessGiveupEntityList.size(); i++){
				row = sheet.createRow((int) i + 1); 
				JoblessInfoEntity joblessInfo = joblessGiveupEntityList.get(i);
				//姓名
				row.createCell(0).setCellValue(joblessInfo.getName());
				//获取性别名称
				ExcelUtils.getDictNameByCode(row, genderDataList, joblessInfo.getSex(),1);
				//身份证号
				row.createCell(2).setCellValue(joblessInfo.getIdcard());
				//联系方式
				row.createCell(3).setCellValue(joblessInfo.getPhone());
				//获取学历名称
				ExcelUtils.getDictNameByCode(row, educationDataList, joblessInfo.getEducation(),4);
				//失业时间
				row.createCell(5).setCellValue(joblessInfo.getUnemploymentdate());
				//获取是否困难人员名称
				ExcelUtils.getDictNameByCode(row, knryDataList, joblessInfo.getKnry(),6);
				//择业意向
				row.createCell(7).setCellValue(joblessInfo.getRemovedate());
				//获取减少原因名称
				ExcelUtils.getDictNameByCode(row, removereasonDataList, joblessInfo.getRemovereason(),8);
				//减少时间
				row.createCell(9).setCellValue(joblessInfo.getRemovedate());
				//获取街道名称
				ExcelUtils.getDictNameByCode(row, streetDataList, joblessInfo.getStreet(),10);
				//获取社区名称
				ExcelUtils.getDictNameByCode(row, communityDataList, joblessInfo.getCommunity(),11);
				//备注
				row.createCell(12).setCellValue(joblessInfo.getRemark());
			}
			
		}
		 try  
	        {  
	        	response.setContentType(contentType);
	        	response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
	        	OutputStream outputStream = response.getOutputStream();
	            wb.write(outputStream);  
	            outputStream.close();  
	        }  
	        catch (Exception e)  
	        {  
	            e.printStackTrace();  
	        }
		return null;  
	}
	
	
	/*
	 * 导出当年实现就业人员
	 */
	@RequestMapping(value="/exportjoblessRealize")
	@ResponseBody
	public ModelAndView exportLaborInfo(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException{
		
		request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
        String fileName ="当年实现就业人员.xls";//设置导出的文件名称
        String recommendedName = new String(fileName.getBytes(),"iso_8859_1");
        String contentType = "application/vnd.ms-excel";
        JoblessSearchData searchData = new JoblessSearchData();
        String street = request.getParameter("street");
        String community = request.getParameter("community");
        searchData.setStreet(street);
        searchData.setCommunity(community);
        List<JoblessInfoEntity> joblessInfoEntityList = joblessInfoService.joblessRealizeList(searchData);
		// 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(fileName);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  

        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);  
      
        cell = row.createCell(1);  
        cell.setCellValue("性别");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style);  
        
        cell = row.createCell(3); 
        cell.setCellValue("联系方式"); 
        cell.setCellStyle(style);
        
        cell = row.createCell(4);  
        cell.setCellValue("学历"); 
        cell.setCellStyle(style);
        
        cell = row.createCell(5);  
        cell.setCellValue("失业时间");  
        cell.setCellStyle(style);

        cell = row.createCell(6);  
        cell.setCellValue("困难人员");  
        cell.setCellStyle(style);
        
        cell = row.createCell(7);  
        cell.setCellValue("择业意向");  
        cell.setCellStyle(style);
        
        cell = row.createCell(8);  
        cell.setCellValue("减少原因");  
        cell.setCellStyle(style);
        
        cell = row.createCell(9);  
        cell.setCellValue("减少时间");  
        cell.setCellStyle(style);
        
        cell = row.createCell(10);  
        cell.setCellValue("街道");  
        cell.setCellStyle(style);
        
        cell = row.createCell(11);  
        cell.setCellValue("社区");  
        cell.setCellStyle(style);
        
        cell = row.createCell(12);  
        cell.setCellValue("备注");  
        cell.setCellStyle(style);
        
		if(joblessInfoEntityList.size() > 0){
			//性别字典
			List<DictCommonEntity> genderDataList = dictService.findSingleDictDetail("dict_gender");
			//学历字典表
			List<DictCommonEntity> educationDataList = dictService.findSingleDictDetail("dict_education");
			//困难人员字典
			List<DictCommonEntity> knryDataList = dictService.findSingleDictDetail("dict_knry");
			//减少原因字典
			List<DictCommonEntity> removereasonDataList = dictService.findSingleDictDetail("dict_removereason");
			//街道字典表
			List<DictCommonEntity> streetDataList = dictService.findSingleDictDetail("dict_street");
			//社区字典表
			List<DictCommonEntity> communityDataList = dictService.findSingleDictDetail("dict_community");
			for(int i = 0; i < joblessInfoEntityList.size(); i++){
				row = sheet.createRow((int) i + 1); 
				JoblessInfoEntity joblessInfo = joblessInfoEntityList.get(i);
				 row.createCell(0).setCellValue(joblessInfo.getName());
				 //获取性别名称
				 ExcelUtils.getDictNameByCode(row, genderDataList, joblessInfo.getSex(),1);
				 row.createCell(2).setCellValue(joblessInfo.getIdcard());
				 row.createCell(3).setCellValue(joblessInfo.getPhone());
				 //获取学历名称
				 ExcelUtils.getDictNameByCode(row, educationDataList, joblessInfo.getEducation(),4);
				 row.createCell(5).setCellValue(joblessInfo.getUnemploymentdate());
				 //获取困难人员
				 ExcelUtils.getDictNameByCode(row, knryDataList, joblessInfo.getKnry(),6);
				 row.createCell(7).setCellValue(joblessInfo.getIntention());
				 //减少原因
				 ExcelUtils.getDictNameByCode(row, removereasonDataList, joblessInfo.getRemovereason(),8);
				 row.createCell(9).setCellValue(joblessInfo.getRemovedate());
				 //获取街道名称
				 ExcelUtils.getDictNameByCode(row, streetDataList, joblessInfo.getStreet(),10);
				 //获取街道名称
				 ExcelUtils.getDictNameByCode(row, communityDataList, joblessInfo.getCommunity(),11);
				 row.createCell(12).setCellValue(joblessInfo.getRemark());
			}
			
		}
		 try  
	        {  
	        	response.setContentType(contentType);
	        	response.setHeader("Content-Disposition", "attachment; filename="+recommendedName);//
	        	OutputStream outputStream = response.getOutputStream();
	            wb.write(outputStream);  
	            outputStream.close();  
	        }  
	        catch (Exception e)  
	        {  
	            e.printStackTrace();  
	        }
		return null;  
	}

  
}
